package com.laoyu.jdbc;

import org.junit.Before;
import org.junit.Test;

import java.sql.*;
import java.util.Scanner;

public class JdbcDemo01 {
  private Connection connection;
    // 建立连接
  @Before
    public void getConnection(){
    // 准备参数
    // 驱动
    String driver = "com.mysql.jdbc.Driver";
    // 连接地址
    String  url = "jdbc:mysql://127.0.0.1:3306/jdbcdatabase?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true";
    // 数据库用户名和密码
    String user = "root";
    String password = "1234";
    // 开始链结 加载驱动
    try {
      Class.forName(driver);
      // 获取连接
      connection=DriverManager.getConnection(url,user,password);
    } catch (ClassNotFoundException e) {
      throw new RuntimeException(e);
    } catch (SQLException e) {
      throw new RuntimeException(e);
    }

  }
  // 关闭连接
  public void closeConnection(){
    if (connection!=null){
      try {
        connection.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
  @Test
  public void insert(){
    // 创建Statement
    try {
      Statement statement = connection.createStatement();
      // 准备参数
      String studentName = "Tom";
      int age = 24;
      int sex = 1;
      int gradeId = 4;
      String phone = "18677784446";
      String address = "安徽合肥";
      // 编写sql语句
      StringBuffer sql = new StringBuffer();
      sql.append("insert into student (student_name,age,sex,grade_id,phone,address)");
      sql.append("values('"+studentName +"','"+age +"','"+sex+"','"+gradeId+"','"+phone+"','"+address+"')");
      // 执行
      int result = statement.executeUpdate(sql.toString());
      // 根据结果判断
      if(result>=1){
        System.out.println("新增成功...");
      }else{
        System.out.println("新增失败....");
      }
    } catch (SQLException e) {
      throw new RuntimeException(e);
    }
  }
  // 修改数据
  @Test
  public void update(){
    // 创建Statement对象
    try {
      Statement statement = connection.createStatement();
      // 准备参数
      int id = 2;
      // 修改姓名和性别
      String studentName = "八嘎";
      int sex = 0;
      // 编写sql语句
      StringBuffer sql = new StringBuffer();
      sql.append("update student set student_name = '"+ studentName +"',sex="+ sex +" ");
      sql.append("where id = "+id+"");
      // 执行
      int result = statement.executeUpdate(sql.toString());
      // 根据结果进行判断
      if(result>=1){
        System.out.println("修改陈功...");
      }else{
        System.out.println("修改失败...");
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
  @Test
  public void delete(){
    // 创建Statement对象
    try {
      Statement statement = connection.createStatement();
      // 准备参数
      int id = 2;
      // 编写sql语句
      String sql = "delete from student where id ="+id+" ";
      // 执行sql语句
     int result = statement.executeUpdate(sql);
     // 处理结果 根据结果进行判断
      if(result>=1){
        System.out.println("删除成功");
      }else{
        System.out.println("删除失败");
      }
    } catch (SQLException e) {
      throw new RuntimeException(e);
    }
  }
  @Test
  public void findAll(){
    // 创建Statement对象
    try {
      Statement statement = connection.createStatement();
      // 编写sql语句
      String sql = "select id,student_name,phone,address from student";
      // 执行
     ResultSet resultSet =statement.executeQuery(sql);
      // 遍历
      while(resultSet.next()){
        int id = resultSet.getInt("id");
        String studentName = resultSet.getString("student_name");
        String phone = resultSet.getString("phone");
        String address = resultSet.getString("address");
        System.out.println(id+"-"+studentName+"-"+phone+"-"+address);
      }
    } catch (SQLException e) {
     e.printStackTrace();
    }
  }
  // 查询数据
  @Test
  public void selectAll(){
    // 获取statement对象
    try {
      Statement statement = connection.createStatement();
      // 编写语句
      String sql = "select * from student";
      // 执行sql语句
      ResultSet resultSet = statement.executeQuery(sql);
      // 处理结果, 遍历
      while(resultSet.next()){
        // 可直接传递下标,但是可读性差 但是简单,尽量不用,
        int id =  resultSet.getInt(1);
        String studentName = resultSet.getString(2);
        String phone = resultSet.getString(3);
        String address = resultSet.getString(4);
        System.out.println(id+"-"+studentName+"-"+phone+"-"+address);
      }
    } catch (SQLException e) {
      throw new RuntimeException(e);
    }
  }
  // SQL注入问题
  // 登录
  @Test
  public void login(){
    // 创建statement对象
    try {
      Statement statement = connection.createStatement();
      //准备参数
      Scanner sc = new Scanner(System.in);
      System.out.println("请输入用户名...");
      String userName = sc.next();
      System.out.println("请输入密码...");
      String passWord = sc.next();
      // 编写sql语句
      StringBuffer stringBuffer = new StringBuffer();
      stringBuffer.append("select * from sys_user");
      stringBuffer.append("where user_name = '"+userName+"'and password = '"+passWord+"' ");
      // 执行sql语句
      ResultSet resultSet = statement.executeQuery(stringBuffer.toString());
      // 进行判断
      if(resultSet.next()){
        System.out.println("登陆成功");
      }else{
        System.out.println("登陆失败");
      }
    } catch (SQLException e) {
      throw new RuntimeException(e);
    }
  }
}
